"""
在线词典服务端 数据处理模型
"""
import pymysql
import hashlib


def encrytion(passwd):
    hash = hashlib.sha256()  # 生成加密对象
    hash.update(passwd.encode())  # 加密转换
    return hash.hexdigest()  # 得到结果


# 数据库操作类
class DictDB:
    def __init__(self):
        self.kwargs = {
            "user": "root",
            "password": "123456",
            "database": "dict",
            "charset": "utf8"
        }
        self._connect()

    def _connect(self):
        # 连接数据库
        self.db = pymysql.connect(**self.kwargs)
        self.cur = self.db.cursor()

    def close(self):
        # 关闭
        self.cur.close()
        self.db.close()

    def register(self, name, passwd):  # 将申请的用户插入到mysql中(用户字段设置为不可重复)
        passwd = encrytion(passwd)  # 加密
        sql = "insert into user (user,passwd) values (%s,%s);"
        try:
            self.cur.execute(sql, [name, passwd])  # 插入用户数据
            self.db.commit()  # 提交
            return True
        except Exception as e:
            print(e)
            self.db.rollback()  # 回滚
            return False

    def login(self, name, passwd):  # 查找该用户在表中的信息
        passwd = encrytion(passwd)  # 转换
        sql = "select id from user where binary user=%s and passwd=%s;"
        self.cur.execute(sql, [name, passwd])
        # 判断是否查询到了
        if self.cur.fetchone():  # 获取查询结果
            return True
        else:
            return False

    def query(self, name, word):  # 查询单词
        sql = "select id,mean from words where word=%s;"
        self.cur.execute(sql, [word])
        result = self.cur.fetchone()  # (id,mean) None 查到了返回元祖
        # 查到单词
        if result:  # 如果为真发送用户名和单词id
            self.insert_hist(name, result[0])  #
            return result[1]

    def insert_hist(self, name, words_id):   # 在关联关系表中插入用户id和单词id
        # 找到user_id
        sql = "select id from user where user=%s;"  # 利用用户名找到用户id
        self.cur.execute(sql, [name])  # 获取用户id
        user_id = self.cur.fetchone()[0]  # (id,)
        # 插入历史记录
        sql = "insert into history (user_id,words_id) values (%s,%s);"
        self.cur.execute(sql, [user_id, words_id])  # 插入用户id和单词编号
        self.db.commit()

    def history(self, name):  # 多表查询 查找用户名、单词和时间
        #  name  word  time
        sql = "select user,word,time " \
              "from user inner join history " \
              "on user.id=history.user_id " \
              "inner join words " \
              "on words.id=history.words_id " \
              "where user=%s " \
              "order by time desc " \
              "limit 10;"
        self.cur.execute(sql, [name])
        return self.cur.fetchall()
